﻿using System;
using System.IO;
using System.Windows.Forms;
using Finisar.SQLite;

namespace MyMovies
{
    internal class SqlLiteManagement
    {
        public static SQLiteConnection Connection { get { return conn; } }

        private static SQLiteConnection conn = null;
        private static SQLiteDataReader reader;

        public static bool CreateDB(string dbName)
        {
            bool bExist = File.Exists(dbName);

            try
            {
                conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;New={1};Compress=True;", dbName, bExist ? "False" : "True"));
                conn.Open();
            }
            catch (SQLiteException exc)
            {
                MessageBox.Show(exc.Message);
            }

            if (!bExist)
            {
                bool bCreate = true;

                bCreate = bCreate && TableMovies.Create();
                bCreate = bCreate && TableGenre.Create();
                bCreate = bCreate && TableVideoFileInfo.Create();
                bCreate = bCreate && TableAudioFileInfo.Create();

                if (!bCreate && conn.State == System.Data.ConnectionState.Open)
                    conn.Close();
            }
            return conn != null && conn.State == System.Data.ConnectionState.Open;
        }

        public static bool OpenDB(string dbName)
        {
            if (conn != null)
                return true;

            if (!File.Exists(dbName))
                return false;

            try
            {
                conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;New={1};Compress=True;", dbName, "False"));
                conn.Open();
            }
            catch (SQLiteException exc)
            {
                MessageBox.Show(exc.Message);
                return false;
            }

            return true;
        }

        public static void CloseDB()
        {
            if (conn != null)
                conn.Close();

            conn = null;
        }

        public static int ExecuteNonQuery(string pCmdText)
        {
            return ExecuteNonQuery(pCmdText, null);
        }

        public static int ExecuteNonQuery(string pCmdText, SQLiteParameter[] parameters)
        {
            SQLiteCommand comm = new SQLiteCommand(pCmdText, conn);
            if (parameters != null)
                foreach (SQLiteParameter param in parameters)
                    comm.Parameters.Add(param);
            try
            {
                return comm.ExecuteNonQuery();
            }
            catch (SQLiteException exc)
            {
                MessageBox.Show(exc.Message, "ExecuteNonQuery");
            }
            return -1;
        }

        public static object ExecuteScalar(string pCmdText)
        {
            return ExecuteScalar(pCmdText, null);
        }

        public static object ExecuteScalar(string pCmdText, SQLiteParameter[] parameters)
        {
            SQLiteCommand comm = new SQLiteCommand(pCmdText, conn);
            if (parameters != null)
                foreach (SQLiteParameter param in parameters)
                    comm.Parameters.Add(param);
            try
            {
                return comm.ExecuteScalar();
            }
            catch (SQLiteException exc)
            {
                MessageBox.Show(exc.Message, "ExecuteScalar");
            }
            return -1;
        }

        public static SQLiteDataReader ExecuteReader(string pCmdText)
        {
            return ExecuteReader(pCmdText, null);
        }

        public static SQLiteDataReader ExecuteReader(string pCmdText, SQLiteParameter[] parameters)
        {
            SQLiteCommand comm = new SQLiteCommand(pCmdText, conn);
            if (parameters != null)
                foreach (SQLiteParameter param in parameters)
                    comm.Parameters.Add(param);
            try
            {
                reader = comm.ExecuteReader();
                return reader;
            }
            catch (SQLiteException exc)
            {
                MessageBox.Show(exc.Message, "ExecuteReader");
            }
            return null;
        }

        public static bool Read()
        {
            if (reader != null)
                return reader.Read();
            else
                return false;
        }

        public static void ReaderClose()
        {
            if (reader != null)
            {
                reader.Close();
                reader = null;
            }
        }

        public static T GetValue<T>(string column)
        {
            try
            {
                if (reader != null)
                    return (T)Convert.ChangeType(reader[column], typeof(T));
                else
                    return default(T);
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message, "ExecuteReader");
            }
            return default(T);
        }
    }
}